-- @owner:yangjian
-- @date:2024-3-27
-- @testpoint:1.去除大对象使用限制，测试lo_open函数使用，合理报错

--清理环境
CREATE OR REPLACE FUNCTION f_opengauss_large_object_function_case0005_1()  
RETURNS VOID AS 
$$
BEGIN  
    BEGIN
    PERFORM lo_unlink(100);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(-2147483648);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(4294967296);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(-1);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(200);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(1);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
	BEGIN
    PERFORM lo_unlink(2);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
END;  
$$
LANGUAGE plpgsql;
/

select f_opengauss_large_object_function_case0005_1();

--环境准备
drop table if exists t_opengauss_large_object_function_case0005_2 cascade;
CREATE TABLE t_opengauss_large_object_function_case0005_2 (a int);
insert into t_opengauss_large_object_function_case0005_2 values (100);

--step1:创建表存储函大对象信息;expect:成功
drop table if exists t_opengauss_large_object_function_case0005_1 cascade;
CREATE TABLE t_opengauss_large_object_function_case0005_1 (loid oid,fd int);

--step2:测试oid参数为正常正数;expect:成功
INSERT INTO t_opengauss_large_object_function_case0005_1 (loid) SELECT lo_create(100);
SELECT lo_open(cast(100 as oid),CAST(x'20000' | x'40000' AS integer));

--step3:测试oid参数为0;expect:成功
INSERT INTO t_opengauss_large_object_function_case0005_1 (loid) SELECT lo_create(0);
SELECT lo_open(loid,CAST(x'20000' | x'40000' AS integer)) from t_opengauss_large_object_function_case0005_1;

--step4:测试oid参数为负数;expect:成功
INSERT INTO t_opengauss_large_object_function_case0005_1 (loid) SELECT lo_create(-1);
SELECT lo_open(loid,CAST(x'20000' | x'40000' AS integer)) from t_opengauss_large_object_function_case0005_1;

--step5:测试oid参数为oid边界值;expect:成功
INSERT INTO t_opengauss_large_object_function_case0005_1 (loid) SELECT lo_create(-2147483648);
SELECT lo_open(cast(-2147483648 as oid),CAST(x'20000' | x'40000' AS integer));

--step6:测试oid参数为不存在的大对象;expect:失败
SELECT lo_open(cast(300 as oid),CAST(x'20000' | x'40000' AS integer));

--step7:测试oid参数为违规值;expect:失败
SELECT lo_open('a',CAST(x'20000' | x'40000' AS integer));
SELECT lo_open(CAST(x'20000' | x'40000' AS integer));
SELECT lo_open(@,CAST(x'20000' | x'40000' AS integer));
SELECT lo_open("1",CAST(x'20000' | x'40000' AS integer));
SELECT lo_open("%*$%#$%$^%$",CAST(x'20000' | x'40000' AS integer));
SELECT lo_open(2147483646547656754676547667547465745674575647576574567654754675467564754757644345345434444447657567564756754674567546735435346547567456456444446666666,CAST(x'20000' | x'40000' AS integer));

--step8:测试oid参数为空值;expect:成功
SELECT lo_open('',CAST(x'20000' | x'40000' AS integer));
SELECT lo_open(null,CAST(x'20000' | x'40000' AS integer));

--step9:测试oid参数为表的列;expect:成功
SELECT lo_open(a,CAST(x'20000' | x'40000' AS integer)) from t_opengauss_large_object_function_case0005_2;

--step10:测试mode参数为只读;expect:成功
SELECT lo_open(100,CAST(x'20000' AS integer));

--step11:测试mode参数为只写;expect:成功
SELECT lo_open(100,CAST(x'40000' AS integer));

--step12:测试mode参数为错误格式;expect:失败
SELECT lo_open(100,1);
SELECT lo_open(100,^%$^##$%@#$@%$);
SELECT lo_open(100,5465464564354354356546435344543534674576654634543543643674657435435);
SELECT lo_open(100,CAST(x'7000' | x'8000' AS integer));
SELECT lo_open(100,0);

--step13:测试mode参数为空值;expect:成功
SELECT lo_open(100,null);
SELECT lo_open(100,'');

--step14:测试oid参数超过oid边界值;expect:失败
SELECT lo_open(cast(4294967296 as oid),CAST(x'20000' | x'40000' AS integer));
SELECT lo_open(cast(-2147483649 as oid),CAST(x'20000' | x'40000' AS integer));


--清理环境
select lo_unlink(loid) from t_opengauss_large_object_function_case0005_1;
select f_opengauss_large_object_function_case0005_1();
drop table if exists t_opengauss_large_object_function_case0005_1 cascade;
drop table if exists t_opengauss_large_object_function_case0005_2 cascade;
drop function f_opengauss_large_object_function_case0005_1();
